﻿using System;
using System.Collections.Generic;
using RTSoft.Modules;
using System.Data.SqlClient;
using System.Data;

namespace RTSoft.SoureCode.Control.KhachHang
{
    class khachhangctrl
    {
        DataProvider provider = new DataProvider();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader ;
        String strSQL = "";

        public void getHeader(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from KHACHHANG where 0=1";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }
        public void getDataByID(ref String errConnect, ref String errSQL, ref DataTable tbl,int id)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from KHACHHANG Where id_kh = "+id;
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getData(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from KHACHHANG ";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getTop10(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select top 10 * from KHACHHANG order by DIEM DESC";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public int getPoint(ref String errConnect, ref String errSQL, int ID)
        {
            int price = 0;
            try
            {
                cmd.Connection = provider.Connection(ref errConnect);
                strSQL = "select DIEM from KHACHHANG WHERE ID_KH = @ID";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@ID", ID);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    price = Convert.ToInt32(reader[0]);
                }
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
            return price;
        }

        
        public void Insert(ref String errConnect, ref String errSQL, khachhanginfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Insert into KHACHHANG";
                strSQL = strSQL + "(TEN_KH,SDT_KH,CMND_KH,DIEM)";
                strSQL = strSQL + " values";
                strSQL = strSQL + "(@TEN_KH,@SDT_KH,@CMND_KH,@DIEM)";
                cmd.CommandText = strSQL;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@TEN_KH", info.TEN_KH);
                cmd.Parameters.AddWithValue("@SDT_KH", info.SDT_KH);
                cmd.Parameters.AddWithValue("@CMND_KH", info.CMND_KH);
                cmd.Parameters.AddWithValue("@DIEM", info.DIEM);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }
        public void UpdatePoint(ref String errConnect, ref String errSQL, khachhanginfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update KHACHHANG SET  ";
                strSQL = strSQL + "DIEM=@DIEM Where  ID_KH = @ID_KH";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@ID_KH", info.ID_KH);               
                cmd.Parameters.AddWithValue("@DIEM", info.DIEM);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

       

        public void Update(ref String errConnect, ref String errSQL, khachhanginfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update KHACHHANG SET  ";
                strSQL = strSQL + "TEN_KH=@TEN_KH,SDT_KH=@SDT_KH,CMND_KH=@CMND_KH,DIEM=@DIEM Where  ID_KH = @ID_KH";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@ID_KH", info.ID_KH);
                cmd.Parameters.AddWithValue("@TEN_KH", info.TEN_KH);
                cmd.Parameters.AddWithValue("@SDT_KH", info.SDT_KH);
                cmd.Parameters.AddWithValue("@CMND_KH", info.CMND_KH);
                cmd.Parameters.AddWithValue("@DIEM", info.DIEM);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction() ;
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Delete(ref String errConnect, ref String errSQL, int id)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                strSQL = "DELETE FROM KHACHHANG WHERE ID_KH=@RowID";
                cmd.Parameters.AddWithValue("@RowID", id);
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
                //' Thuc hien transaction 
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
    }
}
